package ldh.im.fxbase.dao;

import ldh.im.fxbase.data.dto.ImFileSearch;
import ldh.im.fxbase.data.pojo.ImFile;
import ldh.im.fxbase.data.pojo.ext.ImFileExt;
import ldh.im.fxbase.data.pojo.status.ImFileStatus;
import ldh.im.fxbase.util.DbUtil;
import ldh.im.fxbase.util.StringUtil;
import ldh.im.util.DateUtil;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class ImFileDao extends BaseDao {

    private static final Logger LOGGER = LoggerFactory.getLogger(ImFileDao.class);

    private static final String COLLECT_COLUMNS = "id, currentUserId, sessionId, userId, messageId, fileName, fileSize, suffix, path, isRelativePath relativePath," +
            " status statusValue, createTime, updateTime";

    public void insert(ImFile imFile) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            ResultSetHandler<Long> resultSetHandler = new ScalarHandler();
            String sql = String.format("insert into im_file(currentUserId, sessionId, userId, messageId, fileName, fileSize, suffix, path, isRelativePath, status, createTime, updateTime) " +
                    "values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            Long id = queryRunner.insert(connection, sql, resultSetHandler, imFile.getCurrentUserId(), imFile.getSessionId(), imFile.getUserId(), imFile.getMessageId(),
                    imFile.getFileName(), imFile.getFileSize(), imFile.getSuffix(), imFile.getPath(), imFile.getRelativePath(), valueEnum(imFile.getStatus()),
                    imFile.getCreateTime(), imFile.getUpdateTime());
            imFile.setId(id);
            return imFile;
        });
    }

    public void delete(ImFile imFile) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            ResultSetHandler<Long> resultSetHandler = new ScalarHandler();
            String sql = String.format("update im_file set status =?, updateTime = ? where id = ? ");
            int n = queryRunner.update(connection, sql, resultSetHandler, valueEnum(ImFileStatus.delete),
                    imFile.getUpdateTime(), imFile.getId());
            if (n != 1) {
                throw new RuntimeException("更新数据失败");
            }
            return imFile;
        });
    }

    public void delete(String userId, String sessionId, String messageId) throws SQLException {
        DbUtil.transaction((queryRunner, connection) -> {
            ResultSetHandler<Long> resultSetHandler = new ScalarHandler();
            String sql = String.format("update im_file set status =?, updateTime = ? where currentUserId=? and sessionId = ? and messageId = ?");
            int n = queryRunner.update(connection, sql, resultSetHandler, valueEnum(ImFileStatus.delete), new Date(), userId, sessionId, messageId);
            if (n != 1) {
                throw new RuntimeException("更新数据失败");
            }
            return n;
        });
    }

    public ImFile getByKey(String userId, String sessionId, String messageId) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_file where currentUserId=? and sessionId = ? and messageId = ? ", COLLECT_COLUMNS);
            ImFileExt imFileExt = queryRunner.query(connection, sql, new BeanHandler<ImFileExt>(ImFileExt.class), userId, sessionId, messageId);
            return imFileExt;
        });
    }

    public ImFile getBySign(String userId, String sign) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String sql = String.format("select %s from im_file where currentUserId=? and md5 = ? ", COLLECT_COLUMNS);
            ImFileExt imFileExt = queryRunner.query(connection, sql, new BeanHandler<ImFileExt>(ImFileExt.class), userId, sign);
            return imFileExt;
        });
    }

    public List<ImFile> search(String userId, ImFileSearch search) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = buildFileWhere(search);
            String order = buildFileOrder(search);
            String sql = String.format("select %s from im_file where currentUserId = ? %s %s limit %s, %s",
                    COLLECT_COLUMNS, where, order, search.getStart(), search.getSize());
            LOGGER.debug("search sql: {}", sql);
            List<ImFileExt> collectExts = null;
            if (StringUtil.isEmpty(search.getSearch())) {
                collectExts = queryRunner.query(connection, sql, new BeanListHandler<ImFileExt>(ImFileExt.class), userId);
            } else {
                collectExts = queryRunner.query(connection, sql, new BeanListHandler<ImFileExt>(ImFileExt.class), userId, "%" + search.getSearch() + "%");
            }

            return collectExts.stream().map(c->c).collect(Collectors.toList());
        });
    }

    public int searchTotal(String userId, ImFileSearch search) throws SQLException {
        return DbUtil.transaction((queryRunner, connection) -> {
            String where = buildFileWhere(search);
            String sql = String.format("select count(*) from im_file where currentUserId = ? %s", where);
            LOGGER.debug("search sql: {}", sql);
            if (StringUtil.isEmpty(search.getSearch())) {
                return queryRunner.query(connection, sql, new ScalarHandler<Long>(), userId).intValue();
            } else {
                return queryRunner.query(connection, sql, new ScalarHandler<Long>(), userId, "%" + search.getSearch() + "%").intValue();
            }
        });
    }

    private String buildFileWhere(ImFileSearch search) {
        String where = "";
        if (!StringUtil.isEmpty(search.getSearch())) where += String.format(" and fileName like ? ");
        if (search.getStartTime() != null) where += String.format(" and createTime >= '%s'", DateUtil.format(search.getStartTime(), "yyyy-MM-dd HH:mm:ss"));
        if (search.getEndTime() != null) where += String.format(" and createTime <= '%s'", DateUtil.format(search.getEndTime(), "yyyy-MM-dd HH:mm:ss"));
        if (search.getSessionId() != null) where += String.format(" and sessionId = '%s'", search.getSessionId());
        if (search.getUserId() != null) where += String.format(" and userId = '%s'", search.getUserId());
        if (search.getStartId() != null && search.isIdDesc()) where += String.format(" and id < %s", search.getStartId());
        if (search.getStartId() != null && !search.isIdDesc()) where += String.format(" and id > %s", search.getStartId());
        if (search.getSuffix() != null && search.getSuffix().size() > 0) {
            where += String.format(" and suffix in (%s)", search.getSuffix().stream().map(s->String.format("'%s'", s))
                    .collect(Collectors.joining(",")));
        }
        if (search.getNoSuffix() != null && search.getNoSuffix().size() > 0) {
            where += String.format(" and suffix not in (%s)", search.getNoSuffix().stream().map(s->String.format("'%s'", s))
                    .collect(Collectors.joining(",")));
        }
        return where;
    }

    private String buildFileOrder(ImFileSearch search) {
        String order = " order by";
        String sizeOrder = search.isSizeDesc() != null ? (search.isSizeDesc() ? " fileSize desc" : " fileSize asc") : null;
        String idOrder = search.isIdDesc() ? " id desc" : " id asc";
        if (sizeOrder != null) {
            return order + sizeOrder + "," + idOrder;
        }
        return order + idOrder;
    }
}
